﻿
using System;
using System.Linq;
using System.Collections.Generic;
using BLToolkit.DataAccess;
using BLToolkit.Mapping;
using BLToolkit.Data.Linq;
namespace Core
{
    [TableName("tblCanBo")]
    public class CanBo
    {
        [Identity, PrimaryKey(1)]
        [MapField("MaCanBo")]
        public int ID { get; set; }
        public string MaSoCanBo { get; set; }
        public string HoTenCanBo { get; set; }
        public string GioiTinh { get; set; }
        public DateTime? NgaySinh { get; set; }
        public string QueQuan { get; set; }
        public int SoCMND { get; set; }
        [Nullable]
        public string SoDienThoai { get; set; }
        public string NoiOHienTai { get; set; }
        public string TrinhDo { get; set; }
        [Nullable]
        public string DanToc { get; set; }
        [Nullable]
        public string TonGiao { get; set; }
        [Nullable]
        public string QuaTrinhDT { get; set; }
        [Nullable]
        public string HinhDaiDien { get; set; }
        public int MaLoaiCB { get; set; }
        public int MaDonVi { get; set; }
        [Nullable]
        public int MaBangCap { get; set; }
        [Nullable]
        public int MaNgach { get; set; }
        public float HeSoLuong { get; set; }
        public float TienBaoHiem { get; set; }
        public float TienPhuCap { get; set; }
        [NullDateTime]
        public DateTime NgayVaoDang { get; set; }

        public string HoKhauThuongTru { get; set; }
        public string QuaTrinhCongTac { get; set; }

        public string LichSuBanThan { get; set; }
        public string QuanHeGiaDinh { get; set; }

        public string DienBienTangLuong { get; set; }

        [MapIgnore]
        public int TuoiHienTai
        {
            get
            {
                return DateTime.Now.Year - NgaySinh.Value.Year;
            }
        }
        [MapIgnore]
        public string LaDangVien
        {
            get
            {
                if (NgayVaoDang != DateTime.MinValue)
                    return "Đảng viên";
                return string.Empty;
            }
        }
        
        // FK__tblCanBo__MaBang__29572725
        [Association(ThisKey = "MaBangCap", OtherKey = "ID", CanBeNull = false)]
        public BangCap BangCap { get; set; }
        // FK__tblCanBo__MaNgac__2A4B4B5E
        [Association(ThisKey = "MaNgach", OtherKey = "ID", CanBeNull = false)]
        public NgachCongChuc NgachCongChuc { get; set; }
        // FK_tblCanBo_tblDonVi
        [Association(ThisKey = "MaDonVi", OtherKey = "ID", CanBeNull = false)]
        public DonVi DonVi { get; set; }
        // FK_tblCanBo_tblloaicb
        [Association(ThisKey = "MaLoaiCB", OtherKey = "ID", CanBeNull = false)]
        public LoaiCanBo LoaiCanBo { get; set; }

        // FK_tblCB_CV_tblcanbo
        [Association(ThisKey = "MaCanBo", OtherKey = "ID", CanBeNull = true)]
        public List<CanBoChucVu> CanBoChucVus { get; set; }
        // FK_tblCB_KL_tblcanbo
        [Association(ThisKey = "MaCanBo", OtherKey = "MaCanBo", CanBeNull = true)]
        public List<CanBoKyLuat> CanBoKyLuats { get; set; }
        // FK_tblCB_KT_tblCanBo1
        [Association(ThisKey = "MaCanBo", OtherKey = "MaCanBo", CanBeNull = true)]
        public List<CanBoKhenThuong> CanBoKhenThuongs { get; set; }
        // FK_tblCB_TL_tblCanBo
        [Association(ThisKey = "MaCanBo", OtherKey = "MaCanBo", CanBeNull = true)]
        public List<CanBoTangLuong> CanBoTangLuongs { get; set; }

        public static IQueryable<CanBo> CanBos
        {
            get
            {
                using (var db = new DataContext())
                {
                    return db.GetTable<CanBo>();
                }
            }
        }
        public static List<CanBo> All()
        {
            return CanBos.Select(x => new CanBo
            {
                ID = x.ID,
                MaSoCanBo=x.MaSoCanBo,
                HoTenCanBo = x.HoTenCanBo,
                GioiTinh = x.GioiTinh,
                NgaySinh = x.NgaySinh,
                QueQuan = x.QueQuan,
                SoCMND = x.SoCMND,
                SoDienThoai = x.SoDienThoai,
                NoiOHienTai = x.NoiOHienTai,
                TrinhDo = x.TrinhDo,
                DanToc = x.DanToc,
                TonGiao = x.TonGiao,
                QuaTrinhDT = x.QuaTrinhDT,
                HinhDaiDien = x.HinhDaiDien,
                MaLoaiCB = x.MaLoaiCB,
                MaDonVi = x.MaDonVi,
                MaBangCap = x.MaBangCap,
                MaNgach = x.MaNgach,
                HeSoLuong = x.HeSoLuong,
                TienBaoHiem = x.TienBaoHiem,
                TienPhuCap = x.TienPhuCap,


                BangCap = x.BangCap,
                DonVi = x.DonVi,
                LoaiCanBo = x.LoaiCanBo,
                NgachCongChuc = x.NgachCongChuc,
                NgayVaoDang =x.NgayVaoDang,
                HoKhauThuongTru = x.HoKhauThuongTru,
                 QuaTrinhCongTac=x.QuaTrinhCongTac,
                 LichSuBanThan=x.LichSuBanThan,
                  QuanHeGiaDinh=x.QuanHeGiaDinh,
                   DienBienTangLuong = x.DienBienTangLuong
            }).ToList();
        }
       
        public static CanBo Single(int id)
        {
            return All().SingleOrDefault(x => x.ID == id);
        }
        public static int Add(CanBo entity)
        {
            using (var db = new DataContext())
            {
                object rs = db.InsertWithIdentity(entity);
                return int.Parse(rs.ToString());
            }
        }
        public static int Update(CanBo entity)
        {
            using (var db = new DataContext())
            {
                return db.Update(entity);
            }
        }
        public static int Save(CanBo entity)
        {
            using (var db = new DataContext())
            {
                if (entity.ID > 0)
                {
                    return db.Update(entity);
                }
                else
                {
                    object rs = db.InsertWithIdentity(entity);
                    return int.Parse(rs.ToString());
                }
            }
        }
        public static int Delete(int id)
        {
            return CanBos.Delete(x => x.ID == id);
        }

        public static List<CanBo> Paging(int pageSize, int pageNum, out int total)
        {
            var query = from item in All()
                        select item;
            total = query.Count();
            var list = query.Skip(pageSize * (pageNum - 1)).Take(pageSize).ToList();
            return list;
        }
        #region ThongKe
        
        public static List<CanBo> Paging_CanBo_DangVien(int pageSize, int pageNum, out int total)
        {

            var query = from item in All_CanBoDangVien()
                        select item;
            total = query.Count();
            var list = query.Skip(pageSize * (pageNum - 1)).Take(pageSize).ToList();
            return list;
        }
        public static List<CanBo> Paging_CanBo_TheoDonVi(int pageSize, int pageNum, int maDonVi, out int total)
        {
            if (maDonVi > 0)
            {
                var query = from item in All_CanBoTheoDonVi(maDonVi)
                            select item;
                total = query.Count();
                var list = query.Skip(pageSize * (pageNum - 1)).Take(pageSize).ToList();
                return list;
            }
            else
            {
                return Paging(pageSize, pageNum, out total);
            }
        }
        public static List<CanBo> Paging_CanBo_TheoLoaiCanBo(int pageSize, int pageNum, int maLoaiCb, out int total)
        {
            if (maLoaiCb > 0)
            {
                var query = from item in All_CanBoTheoLoaiCanBo(maLoaiCb)
                            select item;
                total = query.Count();
                var list = query.Skip(pageSize * (pageNum - 1)).Take(pageSize).ToList();
                return list;
            }
            else
            {
                return Paging(pageSize, pageNum, out total);
            }
        }
        public static List<CanBo> Paging_CanBo_TheoNgachCongChuc(int pageSize, int pageNum, int maNgach, out int total)
        {
            if (maNgach > 0)
            {
                var query = from item in All_CanBoTheoNgach(maNgach)
                            select item;
                total = query.Count();
                var list = query.Skip(pageSize * (pageNum - 1)).Take(pageSize).ToList();
                return list;
            }
            else
            {
                return Paging(pageSize, pageNum, out total);
            }
        }
        public static List<CanBo> Paging_CanBo_TheoBangCap(int pageSize, int pageNum, int maBangCap, out int total)
        {
            if (maBangCap > 0)
            {
                var query = from item in All_CanBoTheoBangCap(maBangCap)
                            select item;
                total = query.Count();
                var list = query.Skip(pageSize * (pageNum - 1)).Take(pageSize).ToList();
                return list;
            }
            else
            {
                return Paging(pageSize, pageNum, out total);
            }
        }
        public static List<CanBo> Paging_CanBo_TheoHeSoLuong(int pageSize, int pageNum, int heSoLuong, out int total)
        {
            if (heSoLuong > 0)
            {
                var query = from item in All_CanBoTheoHeSoLuong(heSoLuong)
                            select item;
                total = query.Count();
                var list = query.Skip(pageSize * (pageNum - 1)).Take(pageSize).ToList();
                return list;
            }
            else
            {
                return Paging(pageSize, pageNum, out total);
            }
        }
        public static List<CanBo> Paging_CanBo_SapVeHuu(int pageSize, int pageNum,  out int total)
        {
            // Sap ve huu = nam ve huu - 5
            // Nu: 55, Nam: 60, GiaoSu: +10, PhoGS: +7, TS: +5
            var listNam = All().Where(x => x.GioiTinh == "Nam" && x.TuoiHienTai > 55 && x.TuoiHienTai <= 60);
            var listNu = All().Where(x => x.GioiTinh == "Nữ" && x.TuoiHienTai > 50 && x.TuoiHienTai <= 55);
            var rs = listNam.Union(listNu).ToList();


            total = rs.Count();
            var list = rs.Skip(pageSize * (pageNum - 1)).Take(pageSize).ToList();
            return list;
        }

        public static List<CanBo> Paging_CanBo_TheoChucVu(int pageSize, int pageNum, int maDonVi, out int total)
        {
            if (maDonVi > 0)
            {
                var query = from item in All().Where(x => x.MaDonVi == maDonVi)
                            select item;
                total = query.Count();
                var list = query.Skip(pageSize * (pageNum - 1)).Take(pageSize).ToList();
                return list;
            }
            else
            {
                return Paging(pageSize, pageNum, out total);
            }
        }
        public static List<CanBo> Paging_CanBo_DuocKhenThuong(int pageSize, int pageNum, out int total)
        {
            var distinct = from x in CanBoKhenThuong.CanBoKhenThuongs select x.MaCanBo;

            var query = from item in All()
                        join maCanBo in Enumerable.Distinct(distinct)
                        on item.ID equals maCanBo

                        select item;

            total = query.Count();
            var list = query.Skip(pageSize * (pageNum - 1)).Take(pageSize).ToList();
            return list;
        }
        public static List<CanBo> Paging_CanBo_BiKyLuat(int pageSize, int pageNum, out int total)
        {
            var distinct = from x in CanBoKyLuat.CanBoKyLuats select x.MaCanBo;

            var query = from item in All()
                        join maCanBo in Enumerable.Distinct(distinct)
                        on item.ID equals maCanBo

                        select item;

            total = query.Count();
            var list = query.Skip(pageSize * (pageNum - 1)).Take(pageSize).ToList();
            return list;
        }
        #endregion
        #region ThongKeTheoMaCanBo
        public static List<ChucVu> ListCanBoChucVu(int id)
        {
            var query = from canBo in CanBoChucVu.CanBoChucVus.Where(x => x.MaCanBo == id)
                        join chucVu in ChucVu.ChucVus
                        on canBo.MaChucVu equals chucVu.ID
                        select new ChucVu { ID = chucVu.ID, TenChucVu = chucVu.TenChucVu };
            return query.ToList();
        }

        public static List<KhenThuong> ListCanBoKhenThuong(int id)
        {
            var query = from canBo in CanBoKhenThuong.CanBoKhenThuongs.Where(x => x.MaCanBo == id)
                        join khenThuong in KhenThuong.KhenThuongs
                        on canBo.MaKhenThuong equals khenThuong.ID
                        select new KhenThuong { ID = khenThuong.ID, MucKhenThuong = khenThuong.MucKhenThuong,
                         };
            return query.ToList();
        }
       
        public static List<KyLuat> ListCanBoKyLuat(int id)
        {
            var query = from canBo in CanBoKyLuat.CanBoKyLuats.Where(x => x.MaCanBo == id)
                        join kyLuat in KyLuat.KyLuats
                        on canBo.MaKiLuat equals kyLuat.ID
                        select new KyLuat { ID = kyLuat.ID, MucKiLuat = kyLuat.MucKiLuat };
            return query.ToList();
        }

        public static List<QuaTrinhTangLuong> ListCanBoTangLuong(int id)
        {
            var query = from canBo in CanBoTangLuong.CanBoTangLuongs.Where(x => x.MaCanBo == id)
                        join qtTangLuong in QuaTrinhTangLuong.QTTangLuongs
                        on canBo.MaQTTangLuong equals qtTangLuong.ID
                        select new QuaTrinhTangLuong { ID = qtTangLuong.ID,  PhanLoai = qtTangLuong.PhanLoai };
            return query.ToList();
        }
        #endregion

        #region Report
        public static List<CanBo> All_CanBoDangVien()
        {
            return All().Where(x => x.NgayVaoDang != DateTime.MinValue).ToList();
        }
        public static List<CanBo> All_CanBoTheoDonVi(int maDonVi)
        {
            return All().Where(x => x.MaDonVi == maDonVi).ToList();
        }
        public static List<CanBo> All_CanBoTheoTuoi(int tuoi)
        {
            return All().Where(x => x.TuoiHienTai > tuoi && x.TuoiHienTai < tuoi + 10).ToList();
        }
        public static List<CanBo> All_CanBoTheoLoaiCanBo(int maLoaiCB)
        {
            return All().Where(x => x.MaLoaiCB == maLoaiCB).ToList();
        }
        public static List<CanBo> All_CanBoTheoNgach(int maNgach)
        {
            return All().Where(x => x.MaNgach == maNgach).ToList();
        }
        public static List<CanBo> All_CanBoTheoBangCap(int maBangCap)
        {
            return All().Where(x => x.MaBangCap == maBangCap).ToList();
        }
        public static List<CanBo> All_CanBoTheoHeSoLuong(int heSoLuong)
        {
            return All().Where(x => x.HeSoLuong == heSoLuong).ToList();
        }
        public static List<CanBo> All_CanBoSapVeHuu()
        {
            // Sap ve huu = nam ve huu - 5
            // Nu: 55, Nam: 60, GiaoSu: +10, PhoGS: +7, TS: +5
            var listNam = All().Where(x => x.GioiTinh == "Nam" && x.TuoiHienTai > 55 && x.TuoiHienTai <= 60);
            var listNu = All().Where(x => x.GioiTinh == "Nữ" && x.TuoiHienTai > 50 && x.TuoiHienTai <= 55);
            var rs = listNam.Union(listNu).ToList();
            return rs;
        }
        public static List<CanBo> All_CanBoDuocKhenThuong()
        {
            var distinct = from x in CanBoKhenThuong.CanBoKhenThuongs select x.MaCanBo;

            var query = from item in All()
                        join maCanBo in Enumerable.Distinct(distinct)
                        on item.ID equals maCanBo
                                                select item;

            
            return query.ToList();
        }
        public static List<CanBo> All_CanBoBiKyLuat()
        {
            var distinct = from x in CanBoKyLuat.CanBoKyLuats select x.MaCanBo;

            var query = from item in All()
                        join maCanBo in Enumerable.Distinct(distinct)
                        on item.ID equals maCanBo

                        select item;

            return query.ToList();
        }
        #endregion
    }
}